package org.example;

import org.junit.Test;
import org.my.stock.util.MyUtil;

import java.beans.PropertyVetoException;
import java.sql.*;

public class MyH2_1 {

    @Test
    public void my_create_table() throws PropertyVetoException, SQLException, ClassNotFoundException {

        Connection connection = MyUtil.getConnection();
        Statement statement = connection.createStatement();

        String code = "CREATE TABLE IF NOT EXISTS my_stock (" +
                "  symbol VARCHAR PRIMARY KEY," +
                "  name VARCHAR(20) NOT NULL)";

        statement.execute(code);

        code = "CREATE TABLE IF NOT EXISTS my_stock_type (" +
                "  id INT PRIMARY KEY AUTO_INCREMENT," +
                //"  name VARCHAR(20) NOT NULL," +
                "  symbol VARCHAR," +
                "  type_name VARCHAR)";

        statement.execute(code);

        statement.close();
        connection.close();

        System.out.println("my_stock my_stock_type ！");
    }

    @Test
    public void my_drop_table() throws PropertyVetoException, SQLException, ClassNotFoundException {

        Connection connection = MyUtil.getConnection();
        Statement statement = connection.createStatement();

        String code = "DROP TABLE IF EXISTS my_stock";;

        statement.execute(code);

        code = "DROP TABLE IF EXISTS my_stock_type";

        statement.execute(code);

        statement.close();
        connection.close();

        System.out.println("DROP my_stock my_stock_type ！");
    }

    @Test
    public void my_alter_table() throws PropertyVetoException, SQLException, ClassNotFoundException {

        Connection connection = MyUtil.getConnection();
        Statement statement = connection.createStatement();

        String code = "CREATE TABLE IF NOT EXISTS my_stock (" +
                "  id INT PRIMARY KEY AUTO_INCREMENT," +
                //"  name VARCHAR(20) NOT NULL," +
                "  symbol VARCHAR)";

        statement.execute(code);

        code = "CREATE TABLE IF NOT EXISTS my_stock_type (" +
                "  id INT PRIMARY KEY AUTO_INCREMENT," +
                //"  name VARCHAR(20) NOT NULL," +
                "  stock_id INT," +
                "  type_name VARCHAR)";

        statement.execute(code);

        statement.close();
        connection.close();

        System.out.println("my_stock my_stock_type ！");
    }

    @Test
    public void my_test_1() throws PropertyVetoException, SQLException, ClassNotFoundException {

        Connection conn = MyUtil.getConnection();

        StringBuilder sb = new StringBuilder();
        String sql = "SELECT symbol FROM type_stock GROUP BY symbol";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        sb.append("[");
        while (rs.next()) {
            //getData_ex(conn, rs.getString(1));
            sb.append("'" + rs.getString(1) + "',");
        }
        sb.append("0");
        sb.append("]");

        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        if (conn != null) conn.close();

        System.out.println(sb.toString());
    }
}






















